 
import requests
import pandas as pd
from datetime import datetime
import os
from pypinyin import lazy_pinyin
import mysql.connector
import json
#  数据库连接配置
db_config = {
    "host": "192.168.1.107",
    "user": "root",
    "password": "123456",
    "database": "gupiao"
}

field_mapping = {
    "f57": "代码",
    "f60": "昨收",
    "f45": "最低",
    "f46": "今开",
    "f44": "最高",
    "f51": "涨停",
    "f52": "跌停",
    "f43": "最新价",
    "f162": "市盈率(静态)",
    "f167": "市净率",
    "f168": "换手率",
    "f50": "量比",
    "f117": "流通股本(亿股)",
    "f116": "总股本(亿股)",
    "f47": "成交量",
    "f48": "成交额"    
}



def insert_stock_data(stock_info, table_name):


    try:
        # 连接到MySQL数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        
        # 准备插入数据的SQL语句
        insert_fields = ', '.join(['daima', 'zuoshou', 'zuidi', 'jinka', 'zuihigh', 'zhangting', 'dieting', 'zuixinjia', 'shiyinlv_jingtai', 'shijinglv', 'huanshoulv', 'liangbi', 'liutongguben_yiguo', 'zongguben_yiguo', 'chengjiao_volume', 'chengjiao_e'])
        placeholders = ', '.join(['%s'] * len(field_mapping))
        insert_sql = f"INSERT INTO {table_name} ({insert_fields}) VALUES ({placeholders})"
        
        # 转换数据类型
        stock_values = [
            str(stock_info["代码"]),
            float(stock_info["昨收"]) if "昨收" in stock_info else None,
            float(stock_info["最低"]) if "最低" in stock_info else None,
            float(stock_info["今开"]) if "今开" in stock_info else None,
            float(stock_info["最高"]) if "最高" in stock_info else None,
            float(stock_info["涨停"]) if "涨停" in stock_info else None,
            float(stock_info["跌停"]) if "跌停" in stock_info else None,
            float(stock_info["最新价"]) if "最新价" in stock_info else None,
            float(stock_info["市盈率(静态)"]) if "市盈率(静态)" in stock_info else None,
            float(stock_info["市净率"]) if "市净率" in stock_info else None,
            float(stock_info["换手率"]) if "换手率" in stock_info else None,
            float(stock_info["量比"]) if "量比" in stock_info else None,
            float(stock_info["流通股本(亿股)"]) if "流通股本(亿股)" in stock_info else None,
            float(stock_info["总股本(亿股)"]) if "总股本(亿股)" in stock_info else None,
            int(stock_info["成交量"]) if "成交量" in stock_info else None,
            float(stock_info["成交额"]) if "成交额" in stock_info else None
        ]
          
        # 插入数据
        res = cursor.execute(insert_sql, stock_values)
        conn.commit()
        print("Data inserted successfully.")
        return res
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("Database connection closed.")


def insert_stock_market(data_info, table_name):
    try:
        # 连接到MySQL数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()

        # 准备插入数据的SQL语句
        fields = ['交易日期', '收盘沪深300', '涨跌幅沪深300', '融资余额', '融资余额占比', '融资买入额', "融资偿还额", '融资净买入', '融券余额', '融券余量', '融券卖出量', '融券偿还量', '融券净卖出', '融资融券余额', '融资融券余额差值']
        insert_fields = ', '.join(fields)
        placeholders = ', '.join(['%s'] * len(fields))
        insert_sql = f"INSERT INTO {table_name} ({insert_fields}) VALUES ({placeholders})"
        
        print(insert_sql)

        # 转换数据类型
        data_values = [
            str(data_info["交易日期"]),
            float(data_info["收盘沪深300"]) if "收盘沪深300" in data_info else None,
            float(data_info["涨跌幅沪深300"]) if "涨跌幅沪深300" in data_info else None,
            float(data_info["融资余额"]) if "融资余额" in data_info else None,
            float(data_info["融资余额占比"]) if "融资余额占比" in data_info else None,
            float(data_info["融资买入额"]) if "融资买入额" in data_info else None,
            float(data_info["融资偿还额"]) if "融资偿还额" in data_info else None,
            float(data_info["融资净买入"]) if "融资净买入" in data_info else None,
            float(data_info["融券余额"]) if "融券余额" in data_info else None,
            float(data_info["融券余量"]) if "融券余量" in data_info else None,
            float(data_info["融券卖出量"]) if "融券卖出量" in data_info else None,
            float(data_info["融券偿还量"]) if "融券偿还量" in data_info else None,
            float(data_info["融券净卖出"]) if "融券净卖出" in data_info else None,
            float(data_info["融资融券余额"]) if "融资融券余额" in data_info else None,
            float(data_info["融资融券余额差值"]) if "融资融券余额差值" in data_info else None,
        ]
        print(data_values)
        # 插入数据
        res = cursor.execute(insert_sql, data_values)
        conn.commit()
        print("stock_market data inserted successfully.")
        return res
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("Database connection closed.")


def insert_stock_bankuai(data_info, table_name):
    try:
        # 连接到MySQL数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()

        # 准备插入数据的SQL语句
        fields = ['data', 'create_date']
        insert_fields = ', '.join(fields)
        placeholders = ', '.join(['%s'] * len(fields))
        insert_sql = f"INSERT INTO {table_name} ({insert_fields}) VALUES ({placeholders})"
        # 转换数据类型
        data_values = [
            json.dumps(data_info),
            data_info["交易日期"] if "交易日期" in data_info else None
        ]
        # 插入数据
        res = cursor.execute(insert_sql, data_values)
        conn.commit()
        print("stock_market data inserted successfully.")
        return res
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("Database connection closed.")



if __name__ == "__main__":
    # ss = {'start_time': '2025-01-01', '最新价': 9.0, '最高': 9.0, '最低': 7.46, '今开': 7.51, '成交量': 109.1869, '成交额': 9.44334215, '量比': 5.99, '涨停': 9.0, '跌停': 6.0, '代码': '688660', '昨收': 7.5, '总股本(亿股)': 120.000006, '流通股本(亿股)': 120.000006, '市盈率(静态)': -18.88, '市净率': 2.22, '换手率': 0.0819}
    # insert_stock_data(ss, "stock_data")
    pass
